The present invention relates to information processing using multidimensional databases, and particularly to creating reports based upon data from a multidimensional database where the desired contents of the report are first defined and the following query is deduced from the report definition.
Relational database systems are well known in the prior art, and include tabular structures and structured query languages used for database queries. To aid in an understanding of the present invention, a glossary of terms is included hereinbelow. Relational database technology has made possible an independence between the way data is physically stored and the way it can be handled logically. Relational technology has been widely accepted in business, industry and engineering as valuable for storing data.
Database systems may also be implemented in technology termed OLAP (On-Line Analytical Processing). A key feature of OLAP is multidimensional view of data. These databases expose data not as entities and associations as does a Relational Database System but as sets of values organized in a so-called hypercube. This hypercube structure allows for easy access to data along different dimensions.
For example, a multidimensional database can organize numeric data along three dimensions: Product, Geography, Measures. The product dimension reflects the hierarchy of products in the organization, for example, Consumer and Enterprise products being further divided into more precise product categories down to the final product SKU. The Geography dimension will reflect the corporate organization from the entire company to countries to sales district or ZIP codes. Last, the Measures dimension captures more or less detailed sources of income and expenses, such as sales revenue, maintenance revenue (that may be grouped in a common “revenue” category), or operational expenses. These three dimensions define a system of coordinates. For each valid set of coordinates in this system such as, for example, “All Products”, “US”, “Revenue”, the multidimensional database is able to retrieve a single value (numeric in most cases) that represents the aggregated value of the specified measure for the specified dimension coordinates.
From a user perspective a most important feature of database management software is the user interface and report creating capability. Reports generally format results in user friendly formats such as graphs, tables, crosstabs, or forms. A novel technique for providing a semantic layer for interpreting and interacting with a relational database is disclosed in commonly assigned U.S. Pat. No. 5,555,403 entitled “RELATIONAL DATABASE ACCESS SYSTEM USING SEMANTICALLY DYNAMIC OBJECTS” which is hereby incorporated by reference for all purposes.
Conventional reporting tools currently require a user to first specify the set of data to be retrieved from the datasource. And only after the data has been retrieved, does the reporting tools facilitate creation of reports on top of this set of data. The act of specifying a set of data to retrieve prior constructing a report is known as “Querying.” Of the many Querying metaphors, the most powerful is the Business Objects™ Query Technique as described in the above-identified U.S. Pat. No. 5,555,403. When performing a query, users must first define the data they wish to retrieve before they are able to generate a report with queried data. If less data is retrieved than is required, then another query must be generated, followed by another generated report. If more data is retrieved than is required, then unnecessary efforts are expended to define a query and to retrieve unwanted data.
There is a need to provide a system and a method which overcomes the disadvantage of conventional reporting tools by effectively generating and building reports from multidimensional databases without unnecessarily retrieving the data from the database before the report is constructed.